#尝试输入各个分院和各个班级的人
from openpyxl import Workbook
from openpyxl.styles import  PatternFill
import pymysql
import time
from matplotlib import pyplot as plt 
import matplotlib
from openpyxl.drawing.image import Image
from openpyxl.styles import Alignment,Border,Font,Side

wb = Workbook()


align = Alignment(horizontal='center',vertical='center')
border = Border(left=Side(style='medium',color='FF000000'),right=Side(style='medium',color='FF000000'),top=Side(style='medium',color='FF000000'),bottom=Side(style='medium',color='FF000000'))

def get_conn(sql,params=None,needCommit=False,needAll=False):
    conn = pymysql.connect(
        host = '127.0.0.1',          
        port = 3306,                   
        user = "root",                   
        password = 'hzkjzyjsxy',                 
        database = 'vote_system',             
        charset = 'utf8'               
    )
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    res = cursor.execute(sql,params)
    if needCommit:
        conn.commit()
        cursor.close()
        conn.close()
        return res>0
    ret = None
    if needAll:
        ret = cursor.fetchall()
    else:
        ret = cursor.fetchone()
    cursor.close()
    conn.close()
    return ret

def get_college_students(date):
    colleges = ['人文分院','外语分院','理工分院','管理分院','经法分院']
    for college in colleges:
        sheet = wb.create_sheet(college)
        sheet.cell(row=1,column=1,value="学号")
        sheet.cell(row=1,column=2,value="姓名")
        sheet.cell(row=1,column=3,value="学院")
        sheet.cell(row=1,column=4,value="班级")
        sheet.cell(row=1,column=5,value="户籍")
        sheet.cell(row=1,column=6,value="当前居住地")
        sheet.cell(row=1,column=7,value="联系电话")
        sheet.cell(row=1,column=8,value="回校时间")
        sheet.cell(row=1,column=9,value="与湖北接触情况")
        sheet.cell(row=1,column=10,value="与温州台州接触情况")
        sheet.cell(row=1,column=11,value="当前身体状况")
        sheet.cell(row=1,column=12,value="其它要说明问题")

        sheet.column_dimensions['A'].width = 11.11
        sheet['A1'].alignment = align
        sheet.column_dimensions['B'].width = 8.88
        sheet['B1'].alignment = align
        sheet.column_dimensions['C'].width = 10
        sheet['C1'].alignment = align
        sheet.column_dimensions['D'].width = 13.19
        sheet['D1'].alignment = align
        sheet.column_dimensions['E'].width = 20
        sheet['E1'].alignment = align
        sheet.column_dimensions['F'].width = 20
        sheet['F1'].alignment = align
        sheet.column_dimensions['G'].width = 20
        sheet['G1'].alignment = align
        sheet.column_dimensions['H'].width = 20
        sheet['H1'].alignment = align
        sheet.column_dimensions['I'].width = 20
        sheet['I1'].alignment = align
        sheet.column_dimensions['J'].width = 20
        sheet['J1'].alignment = align
        sheet.column_dimensions['K'].width = 20
        sheet['K1'].alignment = align
        sheet.column_dimensions['L'].width = 25
        sheet['L1'].alignment = align

        sql = "SELECT `user`.*,plague.physical_condition,plague.physical_condition_desc FROM `user` LEFT JOIN plague on `user`.id = plague.yb_studentid and plague.upload_date=%s WHERE `user`.college = %s"
        ret = get_conn(sql,params=(date,college),needCommit=False,needAll=True)
        m2 = sheet['M2']
        # m2.alignment = align
        m2.value = '#代表与湖北或温州或台州有异常接触且身体有异常的人'
        m2.fill = chocolate
        m2 = sheet['M3']
        m2.value = '#代表身体异常的人'
        m2.fill = red
        m2 = sheet['M4']
        m2.value = '#代表与湖北或温州或台州接触异常的人'
        m2.fill = yellow
        m2 = sheet['M5']
        m2.value = '#代表没有签到的人'
        m2.fill = blue
        for i in range(2,len(ret)):
            brush = white
            if ret[i-2]["physical_condition"] == None:
                brush = blue #说明没有进行问卷
            elif (ret[i-2]["contact_situation"]!=0 or ret[i-2]["contact_wt_situation"]!=0) and ret[i-2]["physical_condition"] !=0:
                brush = chocolate #非常严重，这个人有危险。两个都有
            elif ret[i-2]["physical_condition"] !=0:
                brush  = red
            elif ret[i-2]["contact_situation"]!=0 or ret[i-2]["contact_wt_situation"]!=0:
                brush = yellow
            for j in range(1,13):
                sheet.cell(row=i,column=j).alignment = align
                sheet.cell(row=i,column=j).border = border
            sheet.cell(row=i,column=1,value=ret[i-2]["id"]).fill = brush
            sheet.cell(row=i,column=2,value=ret[i-2]["name"]).fill = brush
            sheet.cell(row=i,column=3,value=ret[i-2]["college"]).fill = brush
            sheet.cell(row=i,column=4,value=ret[i-2]["the_class"]).fill = brush
            sheet.cell(row=i,column=5,value=ret[i-2]["native_place"]).fill = brush
            sheet.cell(row=i,column=6,value=ret[i-2]["live_place"]).fill = brush
            sheet.cell(row=i,column=7,value=ret[i-2]["phone"]).fill = brush
            sheet.cell(row=i,column=8,value=ret[i-2]["back_to_school_date"]).fill = brush
            sheet.cell(row=i,column=9,value=touch_hb_dict.get(ret[i-2]["contact_situation"],None)).fill = brush
            sheet.cell(row=i,column=10,value=touch_wz_dict.get(ret[i-2]["contact_wt_situation"],None)).fill = brush
            sheet.cell(row=i,column=11,value=heathy_dict.get(ret[i-2]["physical_condition"],None)).fill = brush
            sheet.cell(row=i,column=12,value=ret[i-2]["physical_condition_desc"]).fill = brush

def get_class_students(date):
    sql = "SELECT DISTINCT  `user`.the_class from `user`"
    ret = get_conn(sql=sql,needAll=True)
    for i in ret:
        sheet = wb.create_sheet(i['the_class'])
        sheet.cell(row=1,column=1,value="学号")
        sheet.cell(row=1,column=2,value="姓名")
        sheet.cell(row=1,column=3,value="学院")
        sheet.cell(row=1,column=4,value="班级")
        sheet.cell(row=1,column=5,value="户籍")
        sheet.cell(row=1,column=6,value="当前居住地")
        sheet.cell(row=1,column=7,value="联系电话")
        sheet.cell(row=1,column=8,value="回校时间")
        sheet.cell(row=1,column=9,value="与湖北接触情况")
        sheet.cell(row=1,column=10,value="与温州台州接触情况")
        sheet.cell(row=1,column=11,value="当前身体状况")
        sheet.cell(row=1,column=12,value="其它要说明问题")
        sheet.column_dimensions['A'].width = 11.11
        sheet['A1'].alignment = align
        sheet.column_dimensions['B'].width = 8.88
        sheet['B1'].alignment = align
        sheet.column_dimensions['C'].width = 10
        sheet['C1'].alignment = align
        sheet.column_dimensions['D'].width = 13.19
        sheet['D1'].alignment = align
        sheet.column_dimensions['E'].width = 20
        sheet['E1'].alignment = align
        sheet.column_dimensions['F'].width = 20
        sheet['F1'].alignment = align
        sheet.column_dimensions['G'].width = 20
        sheet['G1'].alignment = align
        sheet.column_dimensions['H'].width = 20
        sheet['H1'].alignment = align
        sheet.column_dimensions['I'].width = 20
        sheet['I1'].alignment = align
        sheet.column_dimensions['J'].width = 20
        sheet['J1'].alignment = align
        sheet.column_dimensions['K'].width = 20
        sheet['K1'].alignment = align
        sheet.column_dimensions['L'].width = 25
        sheet['L1'].alignment = align

        sql = "SELECT `user`.*,plague.physical_condition,plague.physical_condition_desc from `user` left join plague on `user`.id = plague.yb_studentid and plague.upload_date=%s where `user`.the_class=%s"
        ret = get_conn(sql=sql,params=(date,i['the_class']),needAll=True)
        m2 = sheet['M2']
        m2.value = '#代表与湖北或温州或台州有异常接触且身体有异常的人'
        m2.fill = chocolate
        m2 = sheet['M3']
        m2.value = '#代表身体异常的人'
        m2.fill = red
        m2 = sheet['M4']
        m2.value = '#代表与湖北或温州或台州接触异常的人'
        m2.fill = yellow
        m2 = sheet['M5']
        m2.value = '#代表没有签到的人'
        m2.fill = blue
        for i in range(2,len(ret)):
            for j in range(1,13):
                sheet.cell(row=i,column=j).alignment = align
                sheet.cell(row=i,column=j).border = border
            brush = white
            if ret[i-2]["physical_condition"] == None:
                brush = blue #说明没有进行问卷
            elif (ret[i-2]["contact_situation"]!=0 or ret[i-2]["contact_wt_situation"]!=0) and ret[i-2]["physical_condition"] !=0:
                brush = chocolate #非常严重，这个人有危险。两个都有
            elif ret[i-2]["physical_condition"] !=0:
                brush  = red
            elif ret[i-2]["contact_situation"]!=0 or ret[i-2]["contact_wt_situation"]!=0:
                brush = yellow
            sheet.cell(row=i,column=1,value=ret[i-2]["id"]).fill = brush
            sheet.cell(row=i,column=2,value=ret[i-2]["name"]).fill = brush
            sheet.cell(row=i,column=3,value=ret[i-2]["college"]).fill = brush
            sheet.cell(row=i,column=4,value=ret[i-2]["the_class"]).fill = brush
            sheet.cell(row=i,column=5,value=ret[i-2]["native_place"]).fill = brush
            sheet.cell(row=i,column=6,value=ret[i-2]["live_place"]).fill = brush
            sheet.cell(row=i,column=7,value=ret[i-2]["phone"]).fill = brush
            sheet.cell(row=i,column=8,value=ret[i-2]["back_to_school_date"]).fill = brush
            sheet.cell(row=i,column=9,value=touch_hb_dict.get(ret[i-2]["contact_situation"],None)).fill = brush
            sheet.cell(row=i,column=10,value=touch_wz_dict.get(ret[i-2]["contact_wt_situation"],None)).fill = brush
            sheet.cell(row=i,column=11,value=heathy_dict.get(ret[i-2]["physical_condition"],None)).fill = brush
            sheet.cell(row=i,column=12,value=ret[i-2]["physical_condition_desc"]).fill = brush

def output(date):
    save_file = "./"+date+".xlsx"
    get_college_students(date)
    get_class_students(date)
    get_no_vote_students(date)
    get_nerver_vote_students()
    get_join_count_pic()
    wb.save(save_file)

if __name__ == "__main__":
    get_college_students(time.strftime("%Y-%m-%d"))
    get_class_students(time.strftime("%Y-%m-%d"))
    get_no_vote_students(time.strftime("%Y-%m-%d"))
    get_nerver_vote_students()
    get_join_count_pic()
    wb.save("temp.xlsx")